package com.xiyoufang.club.repository;

import com.xiyoufang.club.entity.ClubMembers;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public abstract interface ClubMembersRepository
        extends JpaRepository<ClubMembers, Integer> {
    @Query(nativeQuery = true, value = "SELECT * FROM club_members t WHERE t.club_id=:clubId AND  t.user_id=:userId  LIMIT 1")
    public abstract ClubMembers getClubMembersByClubIdAndUserId(@Param("clubId") int paramInt1, @Param("userId") int paramInt2);

    @Query(nativeQuery = true, value = "SELECT * FROM club_members t WHERE t.club_id=:clubId AND  t.invite_code=:inviteCode  LIMIT 1")
    public abstract ClubMembers getClubMembersByClubIdAndInviteCode(@Param("clubId") int paramInt, @Param("inviteCode") String paramString);

    @Query(nativeQuery = true, value = "SELECT * FROM club_members t WHERE t.club_id=:clubId ORDER BY t.type=2,t.type=3,t.type=1, t.create_time")
    public abstract List<ClubMembers> findClubMembersByClubId(@Param("clubId") int paramInt);

    @Query(nativeQuery = true, value = "SELECT * FROM club_members  WHERE club_id=:clubId AND  user_id IN (:kickOutIds)")
    public abstract List<ClubMembers> findClubMembersByClubIdAndUserIds(@Param("clubId") int paramInt, @Param("kickOutIds") List<Integer> paramList);

    @Query(nativeQuery = true, value = "SELECT count(id) FROM club_members t WHERE t.club_id=:clubId AND  t.type=3")
    public abstract int countClubMembersAdmin(@Param("clubId") int paramInt);

    @Query(nativeQuery = true, value = "SELECT * FROM club_members t WHERE t.club_id=:clubId ORDER BY t.type=2,t.type=3,t.type=1, t.create_time limit :pageNo,:pageSize")
    public abstract List<ClubMembers> findClubMembersByClubId(@Param("clubId") int paramInt1, @Param("pageNo") int paramInt2, @Param("pageSize") int paramInt3);

    @Query(nativeQuery = true, value = "SELECT t.*,i.integral FROM club_members t,club_user_integral i WHERE t.club_id=:clubId and t.club_id=i.club_id and t.user_id=i.user_id and i.integral<0 ORDER BY t.type=2,t.type=3,t.type=1, t.create_time limit :pageNo,:pageSize")
    public abstract List<ClubMembers> findClubNegMembersByClubId(@Param("clubId") int paramInt1, @Param("pageNo") int paramInt2, @Param("pageSize") int paramInt3);

    @Query(nativeQuery = true, value = "SELECT t.*,i.integral FROM club_members t,club_user_integral i WHERE t.club_id=:clubId and t.club_id=i.club_id and t.user_id=i.user_id and i.integral<0 AND (t.p_user_id=:pUserId or t.user_id=:pUserId)ORDER BY t.type=2,t.type=3,t.type=1, t.create_time limit :pageNo,:pageSize")
    public abstract List<ClubMembers> findClubNegMembersByClubIdAndPUserId(@Param("clubId") int paramInt1, @Param("pageNo") int paramInt2, @Param("pageSize") int paramInt3, @Param("pUserId") int paramInt4);

    @Query(nativeQuery = true, value = "SELECT * FROM club_members t WHERE t.club_id=:clubId AND  t.user_id=:userId AND (t.p_user_id=:pUserId or t.user_id=:pUserId) LIMIT 1")
    public abstract ClubMembers getClubMembersByClubIdAndUserIdAndPUserId(@Param("clubId") int paramInt1, @Param("userId") int paramInt2, @Param("pUserId") int paramInt3);

    @Query(nativeQuery = true, value = "SELECT * FROM club_members t WHERE t.club_id=:clubId AND (t.p_user_id=:pUserId or t.user_id=:pUserId) ORDER BY t.type=2,t.type=3,t.type=1, t.create_time")
    public abstract List<ClubMembers> findClubMembersByClubIdAndPUserId(@Param("clubId") int paramInt1, @Param("pUserId") int paramInt2);

    @Query(nativeQuery = true, value = "SELECT * FROM club_members t WHERE t.club_id=:clubId AND (t.p_user_id=:pUserId or t.user_id=:pUserId) AND t.is_partner=0 ORDER BY t.type=2,t.type=3,t.type=1, t.create_time")
    public abstract List<ClubMembers> findClubMembersByClubIdAndPUserIdNoPart(@Param("clubId") int paramInt1, @Param("pUserId") int paramInt2);

    @Query(nativeQuery = true, value = "SELECT * FROM club_members t WHERE t.club_id=:clubId AND (t.p_user_id=:pUserId or t.user_id=:pUserId) ORDER BY t.type=2,t.type=3,t.type=1, t.create_time limit :pageNo,:pageSize")
    public abstract List<ClubMembers> findClubMembersByClubIdAndPUserId(@Param("clubId") int paramInt1, @Param("pageNo") int paramInt2, @Param("pageSize") int paramInt3, @Param("pUserId") int paramInt4);

    @Query(nativeQuery = true, value = "SELECT * FROM club_members t WHERE t.club_id=:clubId AND t.is_partner=1 AND t.user_id=:userId LIMIT 1")
    public abstract ClubMembers getClubPartnerByClubIdAndUserId(@Param("clubId") int paramInt1, @Param("userId") int paramInt2);

    @Query(nativeQuery = true, value = "SELECT * FROM club_members t WHERE t.club_id=:clubId AND t.is_partner=1 AND t.p_user_id=:pUserId AND t.user_id=:userId LIMIT 1")
    public abstract ClubMembers getClubPartnerByClubIdAndUserIdAndPUserId(@Param("clubId") int paramInt1, @Param("userId") int paramInt2, @Param("pUserId") int paramInt3);

    @Query(nativeQuery = true, value = "SELECT * FROM club_members t WHERE t.club_id=:clubId AND  t.is_partner=1")
    public abstract List<ClubMembers> findClubPartnerByClubId(@Param("clubId") int paramInt);

    @Query(nativeQuery = true, value = "SELECT * FROM club_members t WHERE t.club_id=:clubId AND t.is_partner=1 AND t.p_user_id=:pUserId")
    public abstract List<ClubMembers> findClubPartnerByClubIdAndPUserId(@Param("clubId") int paramInt1, @Param("pUserId") int paramInt2);

    @Query(nativeQuery = true, value = "SELECT * FROM club_members t WHERE t.club_id=:clubId AND t.is_partner=1 ORDER BY t.create_time limit :pageNo,:pageSize")
    public abstract List<ClubMembers> findClubPartnerByClubId(@Param("clubId") int paramInt1, @Param("pageNo") int paramInt2, @Param("pageSize") int paramInt3);

    @Query(nativeQuery = true, value = "SELECT * FROM club_members t WHERE t.club_id=:clubId AND t.is_partner=1 AND t.p_user_id=:pUserId ORDER BY t.create_time limit :pageNo,:pageSize")
    public abstract List<ClubMembers> findClubPartnerByClubIdAndPUserId(@Param("clubId") int paramInt1, @Param("pUserId") int paramInt2, @Param("pageNo") int paramInt3, @Param("pageSize") int paramInt4);
}
